package per.vic.attachment.dao;

import org.apache.commons.lang3.time.DateUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import per.vic.attachment.constant.AttachmentConstant;
import per.vic.attachment.model.Attachment;

import javax.annotation.Resource;
import java.sql.*;
import java.util.Date;
import java.util.List;

/**
 * @description: 附件表DAO 不用MYBATIS 是为了减少额外的配置,也方便跨数据库
 * @author: Vic.xu
 * @date: 2019年12月11日 上午10:01:37
 */
@Repository
public abstract class BaseAttachmentDao {


    @Resource
    protected JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    String deleteSql = "delete from " + AttachmentConstant.ATTACHMENT_TABLE + " where id=?";

    String columns = "a.id AS id , a.absolute_path AS absolutePath ,"
            + " a.relative_path AS relativePath , a.temporary AS temporary , a.module AS module ,"
            + " a.content_type AS contentType , a.file_size AS size ,a.file_name AS filename ,"
            + " a.original_name AS originalName , a.create_time AS createTime ";

    public abstract Attachment insert(Attachment attachment);

    /**
     * 判断附件表是否存在,放弃手动建表
     */
    public boolean isAttachmentTableExist() {
        ResultSet rs = null;
        try (Connection conn = jdbcTemplate.getDataSource().getConnection();) {
            rs = null;
            DatabaseMetaData data = conn.getMetaData();
            String[] types = {"TABLE"};
            rs = data.getTables(null, null, AttachmentConstant.ATTACHMENT_TABLE, types);
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    /**
     * 修改附件状态
     *
     * @param id
     * @param temporary 是否是否临时态
     */
    public void updateTemporary(int id, boolean temporary) {
        jdbcTemplate.update("update " + AttachmentConstant.ATTACHMENT_TABLE + " set temporary=? where id=?", temporary, id);
    }

    /**
     * 修改附件状态
     *
     * @param ids:      形如1,2,3
     * @param temporary
     */
    public void updateTemporary(String ids, boolean temporary) {
        jdbcTemplate.update("update " + AttachmentConstant.ATTACHMENT_TABLE + " set temporary=? where id in (" + ids + ")", temporary);
    }


    /**
     * 根据id查询附件
     */
    public Attachment selectAttachmentById(Integer id) {
        String sql = "select " + columns + " FROM " + AttachmentConstant.ATTACHMENT_TABLE + " a  where a.id = ?";
        try {
            Attachment attachment = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Attachment>(Attachment.class), id);
            return attachment;

        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 查询出全部的临时状态的附件：当前时间八小时之前的临时附件
     */
    public List<Attachment> selectTemporaryAttachments() {
        Date date = DateUtils.addHours(new Date(), -8);
        String sql = "select " + columns + " FROM " + AttachmentConstant.ATTACHMENT_TABLE + " a where a.temporary = 1 AND a.create_time < ?";
        List<Attachment> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Attachment>(Attachment.class), date);
        return list;
    }


    /**
     * 删除附件
     */
    public void delete(Integer id) {
        jdbcTemplate.update(deleteSql, id);
    }

    /**
     * 批量删除附件
     * 数据库连接需要配置:rewriteBatchedStatements=true 不然还是逐条执行
     */
    public void delete(List<Integer> ids) {
        jdbcTemplate.batchUpdate(deleteSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, ids.get(i));
            }

            @Override
            public int getBatchSize() {
                return ids.size();
            }
        });
    }

    /**
     * 根据ids查询附件列表
     * @param ids  1，2，3
     */
    public List<Attachment> selectAttachmentsByIds(String ids) {
        String sql = "select " + columns + " FROM " + AttachmentConstant.ATTACHMENT_TABLE + " a  where a.in (" + ids + ")";
        List<Attachment> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Attachment>(Attachment.class));
        return list;
    }
}
